SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		http://it.toolbox.com/people/dimasemensky/?pv=1
-- Create date: 3/24/09
-- Description:	returns a date with last day of the month of passed parameter 
-- Usage Example: SELECT dbo.uf_last_day(GETDATE()), GETDATE() 
-- =============================================
CREATE FUNCTION uf_last_day 
(
	-- Add the parameters for the function here
	@dt as datetime
)
RETURNS datetime
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result datetime
	DECLARE @NextMonth as int
	DECLARE @NextYear as int
	
	SET @NextMonth  = MONTH(@dt)
	SET @NextYear  = YEAR(@dt)
	
	if @NextMonth = 12
	BEGIN
		SET @NextMonth = 1
		SET @NextYear = @NextYear + 1
	END
	ELSE
	BEGIN
		SET @NextMonth = @NextMonth + 1
	END
	

	SELECT @Result = CAST((CAST(@NextMonth as varchar(10)) + '/01/' + CAST(@NextYear as varchar(10))) as datetime)
	SELECT @Result = DATEADD(d, -1, @Result)

	-- Return the result of the function
	RETURN @Result

END
GO

